﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

/// <summary>
/// Populate all action in product table
/// </summary>
public class EF_Product
{
	// Add new product
	public static Int32 CreateProduct(EF_ProductProperty product)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_CreateProduct";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@UserID";
		ProductParam.Value = product.UserID;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Name";
		ProductParam.Value = product.Name;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Description";
		ProductParam.Value = product.Description;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Price";
		ProductParam.Value = product.Price;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@SpecialPrice";
		ProductParam.Value = product.SpecialPrice;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Thumbnail";
		ProductParam.Value = product.Thumbnail;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Image";
		ProductParam.Value = product.Image;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@HightLight";
		ProductParam.Value = product.HightLight;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Promotion";
		ProductParam.Value = product.Promotion;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Status";
		ProductParam.Value = product.Status;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@ProductID";
		ProductParam.Direction = ParameterDirection.Output;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		GenericDataAccess.ExecuteCommand(cmd);

		return Int32.Parse(cmd.Parameters["@ProductID"].Value.ToString());
	}

	// Check duplicated product name
	public static bool checkDuplicatedProductName(string ProductName)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProductByName";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Name";
		ProductParam.Value = ProductName;
		ProductParam.DbType = DbType.String;
		cmd.Parameters.Add(ProductParam);

		DataTable table = GenericDataAccess.ExecuteSelectCommand(cmd);
		if (table.Rows.Count > 0)
			return true;
		return false;
	}

	// Assign product to category
	public static void AssignProductToCategory(int ProductID, string[] CategoryIDs)
	{
		foreach (var CategoryID in CategoryIDs)
		{
			DbCommand cmd = GenericDataAccess.CreateCommand();
			cmd.CommandText = "EF_ProductToCategory";

			DbParameter ProductParam = cmd.CreateParameter();
			ProductParam.ParameterName = "@ProductID";
			ProductParam.Value = ProductID;
			cmd.Parameters.Add(ProductParam);

			ProductParam = cmd.CreateParameter();
			ProductParam.ParameterName = "@CategoryID";
			ProductParam.Value = CategoryID;
			ProductParam.DbType = DbType.Int32;
			cmd.Parameters.Add(ProductParam);

			GenericDataAccess.ExecuteCommand(cmd);
		}
	}

	// Select product
	public static DataTable GetProduct(string PageNumber, out int HowManyPages)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProduct";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@DescriptionLength";
		ProductParam.Value = ElegantFoodConfiguration.ProductBackDescriptionLength;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@PageNumber";
		ProductParam.Value = PageNumber;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@ProductsPerPage";
		ProductParam.Value = ElegantFoodConfiguration.ProductsBackPerPage;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@HowManyProducts";
		ProductParam.Direction = ParameterDirection.Output;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		DataTable table = GenericDataAccess.ExecuteSelectCommand(cmd);
		int HowManyProducts = Int32.Parse(cmd.Parameters["@HowManyProducts"].Value.ToString());
		HowManyPages = (int)Math.Ceiling((double)HowManyProducts / (double)ElegantFoodConfiguration.ProductsBackPerPage);

		return table;
	}

	// Get product by hightlight
	public static DataTable GetProductByHightLight()
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProductByHightLight";

		return GenericDataAccess.ExecuteSelectCommand(cmd);
	}

	// Get product by promotion
	public static DataTable GetProductByPromotion()
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProductByPromotion";

		return GenericDataAccess.ExecuteSelectCommand(cmd);
	}

	// Get product by id
	public static DataTable GetProductById(string id)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProductByid";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@ProductID";
		ProductParam.Value = id;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		return GenericDataAccess.ExecuteSelectCommand(cmd);
	}

	// get product by category id
	public static DataTable GetProductByCategoryId(int id)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_GetProductByCategoryId";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@CategoryID";
		ProductParam.Value = id;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		return GenericDataAccess.ExecuteSelectCommand(cmd);
	}

	// Delete product by id
	public static bool DeleteProduct(int id)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_DeleteProductByID";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@ProductID";
		ProductParam.Value = id;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		if (GenericDataAccess.ExecuteCommand(cmd))
		{
			return true;
		}
		return false;
	}

	public static bool UpdateProduct(EF_ProductProperty product)
	{
		DbCommand cmd = GenericDataAccess.CreateCommand();
		cmd.CommandText = "EF_UpdateProductById";

		DbParameter ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@ProductID";
		ProductParam.Value = product.ProductID;
		ProductParam.DbType = DbType.Int32;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Name";
		ProductParam.Value = product.Name;
		ProductParam.DbType = DbType.String;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Price";
		ProductParam.Value = product.Price;
		ProductParam.DbType = DbType.Decimal;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Promotion";
		ProductParam.Value = product.Promotion;
		ProductParam.DbType = DbType.Boolean;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@HightLight";
		ProductParam.Value = product.HightLight;
		ProductParam.DbType = DbType.Boolean;
		cmd.Parameters.Add(ProductParam);

		ProductParam = cmd.CreateParameter();
		ProductParam.ParameterName = "@Status";
		ProductParam.Value = product.Status;
		ProductParam.DbType = DbType.Boolean;
		cmd.Parameters.Add(ProductParam);

		if (GenericDataAccess.ExecuteCommand(cmd))
		{
			return true;
		}
		return false;
	}
}